package com.zhilei;

import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.parser.PdfTextExtractor;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class PDFToExcel {
    public static void main(String[] args) {
        String rootFolderPath = "C:\\Users\\16833\\Desktop\\PART 1 - EXECUTIVE";  // 替换为你的根文件夹路径
        String excelPath = "C:\\Users\\16833\\Desktop\\test.xlsx"; // 替换为你想要保存的Excel文件路径

        // 创建工作簿和工作表
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("PDF Files");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("序号");
        headerRow.createCell(1).setCellValue("原文件名称");
        headerRow.createCell(2).setCellValue("文件编号");
        headerRow.createCell(3).setCellValue("文件名称（英文）");
        headerRow.createCell(4).setCellValue("文件名称（中文）");
        headerRow.createCell(5).setCellValue("文件类型（清单类、图纸类、规格书等）");
        headerRow.createCell(6).setCellValue("设计专业");
        headerRow.createCell(7).setCellValue("文件路径");
        headerRow.createCell(8).setCellValue("Contract No."); // 添加这一行用于新列

        // 遍历根文件夹及其子文件夹
        int rowCount = 1; // 从第一行后开始写入数据
        rowCount = traverseFolders(rootFolderPath, workbook, sheet, rowCount);

        // 写入Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(excelPath)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
            }
        }

        System.out.println("PDF 文件信息已成功写入 Excel!");
    }

    private static int traverseFolders(String folderPath, Workbook workbook, Sheet sheet, int rowCount) {
        ArrayList<HashMap<String, String>> list = new ArrayList<>();
        File folder = new File(folderPath);
        File[] files = folder.listFiles();
        if (files != null) {
            for (File file : files) {
                if (file.isDirectory()) {
                    // 如果是子文件夹，递归调用继续遍历
                    rowCount = traverseFolders(file.getAbsolutePath(), workbook, sheet, rowCount);
                } else if (file.isFile() && file.getName().endsWith(".pdf")) {
                    // 获取文件名称
                    String fileName = file.getName();

                    // 提取文件编号和文件名称（英文）
                    String fileNumber = null; // 文件编号，初始化为null
                    String fileNameEnglish = ""; // 文件名称（英文）

                    ArrayList<String> strings = new ArrayList<>();
                    strings.add("_0 ");
                    strings.add("_1 ");
                    strings.add("_2 ");
                    strings.add("-0 ");
                    strings.add("01 - ");
                    strings.add("02 - ");
                    strings.add("03 ");
                    strings.add("04 - ");
                    strings.add("05 - ");
                    strings.add("06 - ");
                    strings.add("07 - ");
                    strings.add("08 - ");
                    strings.add("09 - ");
                    strings.add("10 - ");
                    strings.add("11 - ");
                    strings.add("12 - ");
                    strings.add("13 - ");
                    strings.add("14 - ");
                    strings.add("15 - ");

                    boolean foundNumber = false;
                    for (String string : strings) {
                        if (fileName.contains(string)) {
                            // 假设编号和名称用下划线分隔
                            String[] parts = fileName.split(string, 0);
                            if (parts.length > 0) {
                                fileNumber = parts[0] + string;
                            }
                            if (parts.length > 1) {
                                fileNameEnglish = parts[1].replace(".pdf", "");
                            }
                            foundNumber = true;
                            break;
                        }
                    }

                    // 提取Contract No.内容并拆分
                    String[] contractNoParts = extractContractNo(file.getAbsolutePath());
                    String contractNoLabel = contractNoParts[0];
                    String contractNoValue = contractNoParts[1];

                    // 翻译中文文件名
                    String fileNameChinese = translateText(fileNameEnglish);

                    // 假设文件类型为空（可根据实际情况修改）
                    String fileType = "";
                    // 假设设计专业为空（可根据实际情况修改）
                    String designMajor = "";

                    HashMap<String, String> map = new HashMap<>();
                    map.put("0", fileNumber);
                    map.put("1", fileName);
                    map.put("2", fileNameEnglish);
                    map.put("3", file.getAbsolutePath());
                    map.put("4", fileNameChinese);
                    map.put("5", fileType);
                    map.put("6", designMajor);
                    map.put("7", file.getAbsolutePath());
                    map.put("8", contractNoValue); // 将Contract No.值放入HashMap

                    list.add(map);
                }
            }
        }

        for (HashMap<String, String> o : list) {
            // 创建新行并写入数据
            Row row = sheet.createRow(rowCount++);
            row.createCell(0).setCellValue(rowCount - 1);
            row.createCell(1).setCellValue(o.get("1"));
            row.createCell(2).setCellValue(o.get("0"));
            row.createCell(3).setCellValue(o.get("2"));
            row.createCell(4).setCellValue(o.get("4"));
            row.createCell(5).setCellValue(o.get("5"));
            row.createCell(6).setCellValue(o.get("6"));
            row.createCell(7).setCellValue(o.get("7"));
            row.createCell(8).setCellValue(o.get("8")); // 将Contract No.写入Excel新列
        }

        return rowCount;
    }

    private static String[] extractContractNo(String pdfFilePath) {
        String contractNoLabel = "";
        String contractNoValue = "";
        try {
            PdfReader reader = new PdfReader(pdfFilePath);
            int numberOfPages = reader.getNumberOfPages();
            for (int i = 1; i <= numberOfPages; i++) {
                String pageText = PdfTextExtractor.getTextFromPage(reader, i);
                // 使用正则表达式查找Contract No.
                Pattern pattern = Pattern.compile("Contract No.\\s*(.*)");
                Matcher matcher = pattern.matcher(pageText);
                if (matcher.find()) {
                    contractNoLabel = "Contract No.";
                    contractNoValue = matcher.group(1);
                    break;
                }
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new String[]{contractNoLabel, contractNoValue};
    }

    public static String translateText(String textToTranslate) {
        String appId = "3c65cbfd26560483"; // 替换为您的有道翻译API的App ID
        String appSecret = "FwuSsM0HF4u6Abg59BFFEl0ECWPl6Jco"; // 替换为您的您的有道翻译API的App Secret
        String youdaoApiUrl = "https://openapi.youdao.com/api";

        String salt = String.valueOf(System.currentTimeMillis());
        String sign = generateSign(textToTranslate, salt, appSecret);
        Map<String, String> params = new HashMap<>();
        params.put("q", textToTranslate);
        params.put("from", "zh-CHS");
        params.put("to", "EN");
        params.put("appKey", appId);
        params.put("salt", salt);
        params.put("sign", sign);

        try (CloseableHttpClient httpClient = HttpClients.createDefault()) {
            HttpPost post = new HttpPost(youdaoApiUrl);
            StringEntity entity = new StringEntity(mapToUrl(params), StandardCharsets.UTF_8);
            post.setEntity(entity);
            post.setHeader("Content-Type", "application/x-www-form-urlencoded");
            try (CloseableHttpResponse response = httpClient.execute(post)) {
                String responseString = EntityUtils.toString(response.getEntity(), StandardCharsets.UTF_8);

                // 解析返回的JSON字符串，提取翻译结果
                int start = responseString.indexOf("\"translation\"");
                if (start != -1) {
                    start += 15;
                    int end = responseString.indexOf(']', start);
                    if (end != -1) {
                        return responseString.substring(start, end).replace("[", "").replace("]", "");
                    } else {
                        // 处理找不到 ']' 字符的情况，比如返回一个默认值或抛出一个更有针对性的异常
                        return "无法正确提取翻译结果";
                    }
                } else {
                    // 处理找不到 "translation" 字符串的情况，比如返回一个默认值或抛出一个更有针对性的异常
                    return "无法正确提取翻译结果";
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    private static String generateSign(String query, String salt, String appSecret) {
        try {
            MessageDigest md = MessageDigest.getInstance("SHA-256");
            String signStr = query + salt + appSecret;
            byte[] signBytes = md.digest(signStr.getBytes());
            return bytesToHex(signBytes);
        } catch (NoSuchAlgorithmException e) {
            throw new RuntimeException(e);
        }
    }

    private static String mapToUrl(Map<String, String> params) {
        StringBuilder result = new StringBuilder();
        for (Map.Entry<String, String> entry : params.entrySet()) {
            result.append(entry.getKey()).append("=").append(entry.getValue()).append("&");
        }
        return result.substring(0, result.length() - 1);
    }

    private static String bytesToHex(byte[] bytes) {
        StringBuilder hexString = new StringBuilder();
        for (byte b : bytes) {
            String hex = Integer.toHexString(0xff & b);
            if (hex.length() == 1) {
                hexString.append('0');
            }
            hexString.append(hex);
        }
        return hexString.toString();
    }
}